package com.yikome.purchase.service;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.yikome.common.CommonService;
import com.yikome.constant.CommonConstant;
import com.yikome.delivery.enums.DeliveryStatus;
import com.yikome.order.enums.OrderStatus;
import com.yikome.purchase.dto.PendingPurchaseProductExportDTO;
import com.yikome.purchase.entity.PendingPurchase;
import com.yikome.purchase.entity.PendingPurchaseProduct;
import com.yikome.purchase.enums.PurchaseStatus;
import com.yikome.purchase.repository.PendingPurchaseProductRepository;
import com.yikome.purchase.repository.PendingPurchaseRepository;
import com.yikome.purchase.request.PendingPurchaseProductRequest;
import com.yikome.purchase.request.PendingPurchaseRequest;
import com.yikome.utils.NamedParameterJdbcUtils;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableMap;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;

/**
 *  #缺货单
 *  #涉及功能点:
 *  
 *  1.生成缺货单
 *  2.导出缺货单
 *  3.条件查询展示缺货单
 */
@Service
public class PendingPurchaseService extends CommonService<PendingPurchase, Long> {

	@Autowired
	private PendingPurchaseRepository pendingPurchaseRepository ; 
	
	@Autowired
	private PendingPurchaseProductRepository pendingPurchaseProductRepository ; 
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ; 
	
	@Transactional
	public PendingPurchase generatePendingPurchase(PendingPurchaseRequest pendingPurchaseRequest) {
		
		Preconditions.checkArgument(pendingPurchaseRequest.getStorehouseId() != null, "参数信息不全...");
		Preconditions.checkArgument(StrUtil.isNotBlank(pendingPurchaseRequest.getStorehouseName()), "参数信息不全...");
		
		Map<String, Object> paramsMap = new HashMap<>();
		paramsMap.put("orderStatus", OrderStatus.ORDER_STOCK.getStatus());
		paramsMap.put("purchaseStatus", PurchaseStatus.PURCHASE_ASYNC.getStatus());
		paramsMap.put("deliveryStatus", DeliveryStatus.DELIVERY_ASYNC.getStatus());
		paramsMap.put("storehouseId", pendingPurchaseRequest.getStorehouseId());
		
		
		String contentSQL = "SELECT \n" + 
							"	order_vo.*, (IFNULL(order_vo.order_product_quantities,0) + IFNULL(delivery_vo.delivery_quantities,0) - IFNULL(purchase_vo.purchased_quantities,0) - IFNULL(s_inventory.inventory,0))quantity \n" + 
							"FROM  \n" + 
							"( \n" + 
							"	SELECT  \n" + 
							"		o_order.storehouse_id, o_order_product.product_id, o_order_product.product_title, o_order_product.product_sku_id, o_order_product.product_sku_barcode, o_order_product.product_sku_name, o_order_product.product_image_url product_sku_image, \n" + 
							"		o_order_product.source_url, o_order_product.supplier_id, o_order_product.supplier_name, o_order_product.source_id, o_order_product.source_number, o_order_product.reference_purchase_price , SUM(o_order_product.quantity)order_product_quantities \n" + 
							"	FROM o_order \n" + 
							"		LEFT JOIN o_order_product ON (o_order.`code` = o_order_product.order_code AND o_order_product.yn = 1) \n" + 
							"	WHERE 1 = 1 AND o_order.yn = 1 AND o_order.`status` = :orderStatus AND o_order.storehouse_id = :storehouseId \n" + 
							"	GROUP BY o_order_product.product_sku_barcode \n" + 
							") order_vo \n" + 
							"LEFT JOIN  \n" + 
							"( \n" + 
							"	SELECT \n" + 
							"		p_purchase.storehouse_id, p_purchase_product.product_sku_barcode, SUM((p_purchase_product.purchased_quantity - p_purchase_product.storaged_quantity - p_purchase_product.cancel_quantity)) purchased_quantities \n" + 
							"	FROM p_purchase \n" + 
							"		LEFT JOIN p_purchase_product ON (p_purchase.purchase_code = p_purchase_product.purchase_code AND p_purchase_product.yn = 1 ) \n" + 
							"	WHERE p_purchase.yn = 1 AND (p_purchase.`status` = 0 OR p_purchase.`status` = :purchaseStatus) AND p_purchase.storehouse_id = :storehouseId \n" + 
							"	AND (p_purchase_product.purchased_quantity - p_purchase_product.storaged_quantity - p_purchase_product.cancel_quantity) > 0 \n" + 
							"	GROUP BY p_purchase.storehouse_id, p_purchase_product.product_sku_barcode \n" + 
							") purchase_vo ON (order_vo.product_sku_barcode = purchase_vo.product_sku_barcode AND order_vo.storehouse_id = purchase_vo.storehouse_id) \n" + 
							"LEFT JOIN  \n" + 
							"( \n" + 
							"	SELECT  \n" + 
							"		d_delivery.storehouse_id, d_delivery_details.product_sku_barcode , SUM(d_delivery_details.quantity) delivery_quantities \n" + 
							"	FROM d_delivery \n" + 
							"		LEFT JOIN d_delivery_details ON (d_delivery_details.delivery_code = d_delivery.delivery_code AND d_delivery_details.yn = 1) \n" + 
							"	WHERE d_delivery.yn = 1 AND (d_delivery.`status` = 0 OR d_delivery.`status` = :deliveryStatus)  AND d_delivery.storehouse_id = :storehouseId \n" + 
							"	GROUP BY d_delivery.storehouse_id, d_delivery_details.product_sku_barcode \n" + 
							") delivery_vo ON (order_vo.product_sku_barcode = delivery_vo.product_sku_barcode AND order_vo.storehouse_id = delivery_vo.storehouse_id) \n" + 
							"LEFT JOIN s_inventory ON (order_vo.storehouse_id = s_inventory.storehouse_id AND order_vo.product_sku_barcode = s_inventory.product_sku_barcode AND s_inventory.yn = 1) \n" + 
							"WHERE 1 = 1 \n" + 
							"AND (IFNULL(order_vo.order_product_quantities,0) + IFNULL(delivery_vo.delivery_quantities,0) - IFNULL(purchase_vo.purchased_quantities,0) - IFNULL(s_inventory.inventory,0)) > 0 \n"; 
		
		
		List<PendingPurchaseProduct> pendingPurchaseProducts = jdbcTemplate.query(contentSQL, paramsMap, new BeanPropertyRowMapper<>(PendingPurchaseProduct.class));
		
		if(pendingPurchaseProducts == null || pendingPurchaseProducts.isEmpty()) return null ;
		
		PendingPurchase pendingPurchase = new PendingPurchase();
		pendingPurchase.setPendingPurchaseCode(StrUtil.format("{}-{}",  pendingPurchaseRequest.getStorehouseName() , LocalDateTime.now(ZoneId.of("+8")).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))));
		pendingPurchase.setStorehouseId(pendingPurchaseRequest.getStorehouseId());
		
		pendingPurchaseRepository.save(pendingPurchase);
		
		for (PendingPurchaseProduct pendingPurchaseProduct : pendingPurchaseProducts) {
			pendingPurchaseProduct.setPendingPurchaseCode(pendingPurchase.getPendingPurchaseCode());
		}
		pendingPurchaseProductRepository.saveAll(pendingPurchaseProducts);
		
		return pendingPurchase ; 
	}
	
	public List<Map<String , Object>> findPendingPurchasePendingPurchaseCombo() {
		
		String contentSQL = "SELECT pending_purchase_code 'id' , pending_purchase_code 'text', storehouse_id 'attribute' FROM p_pending_purchase WHERE p_pending_purchase.yn = 1 ORDER BY id DESC LIMIT 0 , 20" ;
		return jdbcTemplate.queryForList(contentSQL, ImmutableMap.of());
		
	}
	
	public Page<PendingPurchaseProductExportDTO> findPendingPurchaseProduct(PendingPurchaseProductRequest purchaseProductRequest, Pageable pageable){
		
		Preconditions.checkArgument(StrUtil.isNotBlank(purchaseProductRequest.getPendingPurchaseCode()), "缺货批次不能为空");
		
		String contentSQL = "SELECT \n" + 
							"	product_id, product_title, product_sku_id, product_sku_barcode, product_sku_name, product_sku_image, source_url, source_id, supplier_name, supplier_id, source_number, reference_purchase_price, quantity, pending_purchase_code \n" + 
							"FROM p_pending_purchase_product \n" ; 
		
		StringBuilder paramsSQL = new StringBuilder("WHERE yn = 1 \n");
		Map<String, Object> paramsMap = new HashMap<>();
		
		if(StrUtil.isNotBlank(purchaseProductRequest.getPendingPurchaseCode())) {
			paramsSQL.append("AND pending_purchase_code = :pendingPurchaseCode \n");
			paramsMap.put("pendingPurchaseCode", purchaseProductRequest.getPendingPurchaseCode().trim());
		}
		if(StrUtil.isNotBlank(purchaseProductRequest.getProductSkuBarcode())) {
			paramsSQL.append("AND product_sku_barcode = :productSkuBarcode \n");
			paramsMap.put("productSkuBarcode", purchaseProductRequest.getProductSkuBarcode().trim());
		}
		if(purchaseProductRequest.getProductId() != null) {
			paramsSQL.append("AND product_id = :productId \n");
			paramsMap.put("productId", purchaseProductRequest.getProductId());
		}
		if(purchaseProductRequest.getSupplierId() != null) {
			paramsSQL.append("AND supplier_id = :supplierId \n");
			paramsMap.put("supplierId", purchaseProductRequest.getSupplierId());
		}
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
		String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
		
		List<PendingPurchaseProductExportDTO> content = jdbcTemplate.query(contentSQL + paramsSQL + orderSQL + pageSQL, paramsMap, new BeanPropertyRowMapper<>(PendingPurchaseProductExportDTO.class));
		
		Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contentSQL + paramsSQL + ") T", paramsMap, Long.class);
		
		return new PageImpl<>(content, pageable, total);
	}
	
	public List<PendingPurchaseProductExportDTO> findPendingPurchaseProduct(PendingPurchaseProductRequest purchaseProductRequest){
		
		Preconditions.checkArgument(StrUtil.isNotBlank(purchaseProductRequest.getPendingPurchaseCode()), "缺货批次不能为空");
		
		String contentSQL = "SELECT \n" + 
							"	product_id, product_title, product_sku_id, product_sku_barcode, product_sku_name, product_sku_image, source_url, source_id, supplier_name, supplier_id, source_number, reference_purchase_price, quantity, pending_purchase_code \n" + 
							"FROM p_pending_purchase_product \n" ; 
		
		StringBuilder paramsSQL = new StringBuilder("WHERE yn = 1 \n");
		Map<String, Object> paramsMap = new HashMap<>();
		
		if(StrUtil.isNotBlank(purchaseProductRequest.getPendingPurchaseCode())) {
			paramsSQL.append("AND pending_purchase_code = :pendingPurchaseCode \n");
			paramsMap.put("pendingPurchaseCode", purchaseProductRequest.getPendingPurchaseCode().trim());
		}
		if(StrUtil.isNotBlank(purchaseProductRequest.getProductSkuBarcode())) {
			paramsSQL.append("AND product_sku_barcode = :productSkuBarcode \n");
			paramsMap.put("productSkuBarcode", purchaseProductRequest.getProductSkuBarcode().trim());
		}
		if(purchaseProductRequest.getProductId() != null) {
			paramsSQL.append("AND product_id = :productId \n");
			paramsMap.put("productId", purchaseProductRequest.getProductId());
		}
		if(purchaseProductRequest.getSupplierId() != null) {
			paramsSQL.append("AND product_id = :supplierId \n");
			paramsMap.put("supplierId", purchaseProductRequest.getSupplierId());
		}
		
		String orderSQL = "ORDER BY supplier_id DESC , product_id DESC \n" ; 
		
		List<PendingPurchaseProductExportDTO> content = jdbcTemplate.query(contentSQL + paramsSQL + orderSQL, paramsMap, new BeanPropertyRowMapper<>(PendingPurchaseProductExportDTO.class));
		
		return content ; 
	}

	@Transactional
	public void purchasedPendingPurchase(String pendingPurchaseCode, String productSkuBarcode) {
		
		PendingPurchaseProduct pendingPurchaseProduct = pendingPurchaseProductRepository.findByPendingPurchaseCodeAndProductSkuBarcode(pendingPurchaseCode , productSkuBarcode);
		
		Preconditions.checkArgument(pendingPurchaseProduct != null, "缺货商品不存在...");
		
		pendingPurchaseProduct.setYn(0);
		
		pendingPurchaseProductRepository.save(pendingPurchaseProduct);
		
	}
}
